using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
	public static partial class TeamService
	{
        public static bool AddTeam(Team team)
		{
            string sql =
				"INSERT Team (Title, Purpose, TeamContent, Image, TeamPhoto)" +
				"VALUES (@Title, @Purpose, @TeamContent, @Image, @TeamPhoto)";
				
			sql += " ; SELECT @@IDENTITY";

            
			SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Title", team.Title),
				new SqlParameter("@Purpose", team.Purpose),
				new SqlParameter("@TeamContent", team.TeamContent),
				new SqlParameter("@Image", team.Image),
				new SqlParameter("@TeamPhoto", team.TeamPhoto)
			};
			
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
			if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
            
		}
		
		
        public static bool DeleteTeamByID(int iD)
		{
            string sql = "DELETE Team WHERE ID = @ID";

           	SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};
		
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
            
		}
		
        public static bool ModifyTeam(Team team)
        {
            string sql =
                "UPDATE Team " +
                "SET " +
	                "Title = @Title, " +
	                "Purpose = @Purpose, " +
	                "TeamContent = @TeamContent, " +
	                "Image = @Image, " +
	                "TeamPhoto = @TeamPhoto " +
                "WHERE ID = @ID";

			SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", team.ID),
				new SqlParameter("@Title", team.Title),
				new SqlParameter("@Purpose", team.Purpose),
				new SqlParameter("@TeamContent", team.TeamContent),
				new SqlParameter("@Image", team.Image),
				new SqlParameter("@TeamPhoto", team.TeamPhoto)
			};

			int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }		

        public static IList<Team> GetAllTeams()
        {
            string sqlAll = "SELECT * FROM Team";
			return GetTeamsBySql( sqlAll );
        }
		
        public static Team GetTeamByID(int iD)
        {
            string sql = "SELECT * FROM Team WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};
			
            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
			
			Team team = new Team();
			
            foreach (DataRow dr in temp.Rows)
            {
				team.ID = (int)dr["ID"];
				team.Title = (string)dr["Title"];
				team.Purpose = (string)dr["Purpose"];
				team.TeamContent = (string)dr["TeamContent"];
				team.Image = (string)dr["Image"];
				team.TeamPhoto = (string)dr["TeamPhoto"];
				
            }
            return team;    
            
        }
		
		
        private static IList<Team> GetTeamsBySql(string safeSql)
        {
            List<Team> list = new List<Team>();

			using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
			{
				while (dr.Read())
				{
					Team team = new Team();
					
					team.ID = (int)dr["ID"];
					team.Title = (string)dr["Title"];
					team.Purpose = (string)dr["Purpose"];
					team.TeamContent = (string)dr["TeamContent"];
					team.Image = (string)dr["Image"];
					team.TeamPhoto = (string)dr["TeamPhoto"];
	
					list.Add(team);
				}
			}
            return list;
        }
		
        private static IList<Team> GetTeamsBySql(string safeSql, params SqlParameter[] para)
        {
            List<Team> list = new List<Team>();

			using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
			{
				while (dr.Read())
				{
					Team team = new Team();
					
					team.ID = (int)dr["ID"];
					team.Title = (string)dr["Title"];
					team.Purpose = (string)dr["Purpose"];
					team.TeamContent = (string)dr["TeamContent"];
					team.Image = (string)dr["Image"];
					team.TeamPhoto = (string)dr["TeamPhoto"];
	
					list.Add(team);
				}
			}
            return list;
        }
	}
}
